cd C:\Users\alen_\Dropbox\burning_glass\data


******* Assemble the data 






foreach year of numlist 2013(1)2013 {
foreach num of numlist 1(1)9 {
u "Main_`year'-0`num'", clear

drop bestfitmsa bestfitmsaname bestfitmsatype

sample 10

save "data_`year'_0`num'", replace
}


foreach num of numlist 10(1)12 {
u "Main_`year'-`num'", clear

drop bestfitmsa bestfitmsaname bestfitmsatype

sample 10

save "data_`year'_`num'", replace
}
}



foreach year of numlist 2017(1)2017 {
foreach num of numlist 1(1)9 {
u "Main_`year'-0`num'", clear

drop bestfitmsa bestfitmsaname bestfitmsatype

sample 10

save "data_`year'_0`num'", replace
}


foreach num of numlist 10(1)12 {
u "Main_`year'-`num'", clear

drop bestfitmsa bestfitmsaname bestfitmsatype

sample 10

save "data_`year'_`num'", replace
}
}






foreach year of numlist 2018(1)2022 {
foreach num of numlist 1(1)9 {
u "Main_`year'-0`num'", clear

drop jobid bestfitmsa bestfitmsaname bestfitmsatype

sample 10

save "data_`year'_0`num'", replace
}


foreach num of numlist 10(1)12 {
u "Main_`year'-`num'", clear

drop jobid bestfitmsa bestfitmsaname bestfitmsatype

sample 10

save "data_`year'_`num'", replace
}
}


foreach year of numlist 2023 {
foreach num of numlist 11(1)11 {
u "Main_`year'-`num'", clear

drop jobid bestfitmsa bestfitmsaname bestfitmsatype

sample 10

save "data_`year'_`num'", replace
}
}




foreach year of numlist 2013(1)2013 {
clear all 

foreach num of numlist 1(1)9 {
    
append using data_`year'_0`num'

keep bgtjobid edu minhrlysalary maxhrlysalary minsalary maxsalary fips onet naics3 jobhours taxterm occfamname jobdate

}

foreach num of numlist 10(1)12 {
    
append using data_`year'_`num'

keep bgtjobid edu minhrlysalary maxhrlysalary minsalary maxsalary fips onet naics3 jobhours taxterm occfamname jobdate

}

g college=0
replace college=1 if edu>=16

g hrlysalary=(minhrlysalary+maxhrlysalary)/2
g salary=(minsalary+maxsalary)/2

g ln_hrlysalary=ln(hrlysalary)
g ln_salary=ln(salary)

g wage_flag=0
replace wage_flag=1 if ln_hrlysalary!=.

g year=`year'

duplicates drop bgtjobid, force

save temp_`year', replace
}


foreach year of numlist 2018(1)2022 {
clear all 
cd E:\burning_glass_temp

foreach num of numlist 1(1)9 {
    
append using data_`year'_0`num'

keep bgtjobid edu minhrlysalary maxhrlysalary minsalary maxsalary fips onet naics3 jobhours taxterm occfamname jobdate

}

foreach num of numlist 10(1)12 {
    
append using data_`year'_`num'

keep bgtjobid edu minhrlysalary maxhrlysalary minsalary maxsalary fips onet naics3 jobhours taxterm occfamname jobdate

}

g college=0
replace college=1 if edu>=16

g hrlysalary=(minhrlysalary+maxhrlysalary)/2
g salary=(minsalary+maxsalary)/2

g ln_hrlysalary=ln(hrlysalary)
g ln_salary=ln(salary)

g wage_flag=0
replace wage_flag=1 if ln_hrlysalary!=.

g year=`year'

duplicates drop bgtjobid, force

save temp_`year', replace
}



foreach year of numlist 2023 {
clear all 
foreach num of numlist 1(1)9 {
    
append using data_`year'_0`num'

keep bgtjobid edu minhrlysalary maxhrlysalary minsalary maxsalary fips onet naics3 jobhours taxterm occfamname jobdate
}

foreach num of numlist 10(1)11 {
    
append using data_`year'_`num'

keep bgtjobid edu minhrlysalary maxhrlysalary minsalary maxsalary fips onet naics3 jobhours taxterm occfamname jobdate
}

g college=0
replace college=1 if edu>=16

g hrlysalary=(minhrlysalary+maxhrlysalary)/2
g salary=(minsalary+maxsalary)/2

g ln_hrlysalary=ln(hrlysalary)
g ln_salary=ln(salary)

g wage_flag=0
replace wage_flag=1 if ln_hrlysalary!=.

g year=`year'

duplicates drop bgtjobid, force

save temp_`year', replace

}



**** Employer id


foreach year of numlist 2018(1)2022 {
clear all 

foreach num of numlist 1(1)9 {
    
append using data_`year'_0`num'

keep bgtjobid employer

}

foreach num of numlist 10(1)12 {
    
append using data_`year'_`num'

keep bgtjobid employer

}

duplicates drop bgtjobid, force

save temp_`year'_employer, replace
}



foreach year of numlist 2023 {
clear all 

foreach num of numlist 1(1)5 {
    
append using data_`year'_0`num'

keep bgtjobid employer

}

duplicates drop bgtjobid, force

save temp_`year'_employer, replace
}


clear all
cd C:\Users\alen_\Dropbox\burning_glass\data\
foreach year of numlist 2018(1)2023 {
append using temp_`year'_employer
}

save employer, replace


clear all

foreach year of numlist 2013(1)2023 {
append using temp_`year'
}

keep if ln_hrlysalary!=.

g month_st=substr(jobdate,6,2)
destring month_st, g(month) force
destring fips, g(county) force

cd "C:\Users\alen_\Dropbox\burning_glass\data"

merge m:1 county using county_metrofips
keep if _merge==3
drop _merge

merge m:1 county onet using onet_county_metrofips_emp_2018_q1
keep if _merge==3
drop _merge 

ren onet onetsoccode

g ln_emp=ln(emp)

g post=0
replace post=1 if year>=2020 & year<=2021 
replace post=2 if year>=2022 & year<=2023


g year_educ=edu if edu>=0
egen onet_group=group(onet)
egen jobhours_group=group(jobhours)
egen taxterm_group=group(taxterm)
egen naics_group=group(naics3)

merge 1:1 bgtjobid using "remote_dictionary_2018_01"
drop if _merge==2
drop _merge 

ren remote_dummy_negate remote_job 
drop remote_dummy

merge 1:1 bgtjobid using "remote_dictionary_2018_02"
drop if _merge==2
drop _merge 

replace remote_job=remote_dummy_negate if remote_job==. & remote_dummy_negate!=.
drop remote_dummy remote_dummy_negate

foreach num of numlist 3(1)9 {
merge 1:1 bgtjobid using "remote_dictionary_2018_0`num'"
drop if _merge==2
drop _merge 

replace remote_job=remote_dummy_negate if remote_job==. & remote_dummy_negate!=.
drop remote_dummy remote_dummy_negate
}


foreach num of numlist 10(1)12 {
merge 1:1 bgtjobid using "remote_dictionary_2018_`num'"
drop if _merge==2
drop _merge 


replace remote_job=remote_dummy_negate if remote_job==. & remote_dummy_negate!=.
drop remote_dummy remote_dummy_negate
}

foreach year_num of numlist 2019(1)2022 {
foreach num of numlist 1(1)9 {
merge 1:1 bgtjobid using "remote_dictionary_`year_num'_0`num'"
drop if _merge==2
drop _merge 

replace remote_job=remote_dummy_negate if remote_job==. & remote_dummy_negate!=.
drop remote_dummy remote_dummy_negate
}


foreach num of numlist 10(1)12 {
merge 1:1 bgtjobid using "remote_dictionary_`year_num'_`num'"
drop if _merge==2
drop _merge 

replace remote_job=remote_dummy_negate if remote_job==. & remote_dummy_negate!=.
drop remote_dummy remote_dummy_negate
}
}

ren onetsoccode onet
merge m:1 onet using "remote_dummy_based_on_bg"
drop if _merge==2
drop _merge 

g remote_occ=0
replace remote_occ=1 if dremote>0.15 & dremote<.

cd C:\Users\alen_\Dropbox\burning_glass\data
save temp, replace



***** Create the skill list 


**** 2016 

foreach num of numlist 6(1)9 {
u data_2016_0`num', clear

keep if minhrlysalary!=-999

g hrlysalary=(minhrlysalary+maxhrlysalary)/2
g salary=(minsalary+maxsalary)/2

g ln_hrlysalary=ln(hrlysalary)
g ln_salary=ln(salary)

keep bgtjobid ln_hrlysalary

save job_id_temp, replace

u "Skills_2016-0`num'", clear

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save skill_2016_0`num', replace

}


foreach num of numlist 10(1)12 {
u data_2016_`num', clear

keep if minhrlysalary!=-999

g hrlysalary=(minhrlysalary+maxhrlysalary)/2
g salary=(minsalary+maxsalary)/2

g ln_hrlysalary=ln(hrlysalary)
g ln_salary=ln(salary)

keep bgtjobid ln_hrlysalary

save job_id_temp, replace

u "Skills_2016-`num'", clear

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save skill_2016_`num', replace

}




*** 2018 

foreach num of numlist 1(1)9 {
u data_2018_0`num', clear

keep if minhrlysalary!=-999

g hrlysalary=(minhrlysalary+maxhrlysalary)/2
g salary=(minsalary+maxsalary)/2

g ln_hrlysalary=ln(hrlysalary)
g ln_salary=ln(salary)

keep bgtjobid ln_hrlysalary

save job_id_temp, replace

import delimited "Skills_2018-0`num'.txt", colrange(:3) clear

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save skill_2018_0`num', replace

}


foreach num of numlist 10(1)12 {
u data_2018_`num', clear

keep if minhrlysalary!=-999

g hrlysalary=(minhrlysalary+maxhrlysalary)/2
g salary=(minsalary+maxsalary)/2

g ln_hrlysalary=ln(hrlysalary)
g ln_salary=ln(salary)

keep bgtjobid ln_hrlysalary

save job_id_temp, replace

import delimited "Skills_2018-`num'.txt", colrange(:3) clear

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save skill_2018_`num', replace

}


****** 2019 
foreach num of numlist 1(1)9 {
u data_2019_0`num', clear

keep if minhrlysalary!=-999

g hrlysalary=(minhrlysalary+maxhrlysalary)/2
g salary=(minsalary+maxsalary)/2

g ln_hrlysalary=ln(hrlysalary)
g ln_salary=ln(salary)

keep bgtjobid ln_hrlysalary

save job_id_temp, replace

import delimited "Skills_2019-0`num'.txt", colrange(:3) clear

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save skill_2019_0`num', replace

}


foreach num of numlist 10(1)12 {
u data_2019_`num', clear

keep if minhrlysalary!=-999

g hrlysalary=(minhrlysalary+maxhrlysalary)/2
g salary=(minsalary+maxsalary)/2

g ln_hrlysalary=ln(hrlysalary)
g ln_salary=ln(salary)

keep bgtjobid ln_hrlysalary

save job_id_temp, replace

import delimited "Skills_2019-`num'.txt", colrange(:3) clear

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save skill_2019_`num', replace

}



** 2020



foreach num of numlist 1(1)9 {
u data_2020_0`num', clear

keep if minhrlysalary!=-999

g hrlysalary=(minhrlysalary+maxhrlysalary)/2
g salary=(minsalary+maxsalary)/2

g ln_hrlysalary=ln(hrlysalary)
g ln_salary=ln(salary)

keep bgtjobid ln_hrlysalary

save job_id_temp, replace

import delimited "Skills_2020-0`num'.txt", colrange(:3) clear

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save skill_2020_0`num', replace

}



foreach num of numlist 10(1)12 {
u data_2020_`num', clear

keep if minhrlysalary!=-999

g hrlysalary=(minhrlysalary+maxhrlysalary)/2
g salary=(minsalary+maxsalary)/2

g ln_hrlysalary=ln(hrlysalary)
g ln_salary=ln(salary)

keep bgtjobid ln_hrlysalary

save job_id_temp, replace

import delimited "Skills_2020-`num'.txt", colrange(:3) clear

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save skill_2020_`num', replace

}

** 2021

foreach num of numlist 1(1)9 {
u data_2021_0`num', clear

keep if minhrlysalary!=-999

g hrlysalary=(minhrlysalary+maxhrlysalary)/2
g salary=(minsalary+maxsalary)/2

g ln_hrlysalary=ln(hrlysalary)
g ln_salary=ln(salary)

keep bgtjobid ln_hrlysalary

save job_id_temp, replace

import delimited "Skills_2021-0`num'.txt", colrange(:3) clear

egen job=group(bgtjobid)
keep if job<=1500000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save temp_skill_first_half, replace 

import delimited "Skills_2021-0`num'.txt", colrange(:3) clear

egen job=group(bgtjobid)
keep if job>1500000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

append using temp_skill_first_half

save skill_2021_0`num', replace

}


foreach num of numlist 10(1)12 {
u data_2021_`num', clear

keep if minhrlysalary!=-999

g hrlysalary=(minhrlysalary+maxhrlysalary)/2
g salary=(minsalary+maxsalary)/2

g ln_hrlysalary=ln(hrlysalary)
g ln_salary=ln(salary)

keep bgtjobid ln_hrlysalary

save job_id_temp, replace

import delimited "Skills_2021-`num'.txt", colrange(:3) clear

egen job=group(bgtjobid)
keep if job<=1500000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save temp_skill_first_half, replace 

import delimited "Skills_2021-`num'.txt", colrange(:3) clear

egen job=group(bgtjobid)
keep if job>1500000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

append using temp_skill_first_half

save skill_2021_`num', replace

}


** 2022

foreach num of numlist 4(1)5 {
u data_2022_0`num', clear

keep if minhrlysalary!=-999

g hrlysalary=(minhrlysalary+maxhrlysalary)/2
g salary=(minsalary+maxsalary)/2

g ln_hrlysalary=ln(hrlysalary)
g ln_salary=ln(salary)

keep bgtjobid ln_hrlysalary

save job_id_temp, replace

import delimited "Skills_2022-0`num'.txt", colrange(:3) clear

egen job=group(bgtjobid)
keep if job<=2500000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save temp_skill_first_half, replace 

import delimited "Skills_2022-0`num'.txt", colrange(:3) clear

egen job=group(bgtjobid)
keep if job>2500000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

append using temp_skill_first_half

save skill_2022_0`num', replace

}


**** March 


foreach num of numlist 6(1)9 {
u data_2022_0`num', clear

keep if minhrlysalary!=-999

g hrlysalary=(minhrlysalary+maxhrlysalary)/2
g salary=(minsalary+maxsalary)/2

g ln_hrlysalary=ln(hrlysalary)
g ln_salary=ln(salary)

keep bgtjobid ln_hrlysalary

save job_id_temp, replace

import delimited "Skills_2022-0`num'.txt", colrange(:3) clear

egen job=group(bgtjobid)
keep if job<=1500000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save temp_skill_first_half, replace 


import delimited "Skills_2022-0`num'.txt", colrange(:3) clear

egen job=group(bgtjobid)
keep if job<=3000000 & job>1500000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save temp_skill_first_half2, replace 


import delimited "F:\burning_glass\burning_glass\Skills_2022-0`num'.txt", colrange(:3) clear

egen job=group(bgtjobid)
keep if job>3000000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

append using temp_skill_first_half
append using temp_skill_first_half2

save skill_2022_0`num', replace

}



***********************************************



foreach num of numlist 10(1)12 {
u data_2022_`num', clear

keep if minhrlysalary!=-999

g hrlysalary=(minhrlysalary+maxhrlysalary)/2
g salary=(minsalary+maxsalary)/2

g ln_hrlysalary=ln(hrlysalary)
g ln_salary=ln(salary)

keep bgtjobid ln_hrlysalary

save job_id_temp, replace

import delimited "Skills_2022-`num'.txt", colrange(:3) clear

egen job=group(bgtjobid)
keep if job<=1500000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save temp_skill_first_half, replace 


import delimited "Skills_2022-`num'.txt", colrange(:3) clear

egen job=group(bgtjobid)
keep if job<=3000000 & job>1500000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save temp_skill_first_half2, replace 


import delimited "Skills_2022-`num'.txt", colrange(:3) clear

egen job=group(bgtjobid)
keep if job>3000000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

append using temp_skill_first_half
append using temp_skill_first_half2

save skill_2022_`num', replace

}




foreach num of numlist 6(1)9 {
u Main_2022-0`num', clear

keep if minhrlysalary!=-999

g hrlysalary=(minhrlysalary+maxhrlysalary)/2
g salary=(minsalary+maxsalary)/2

g ln_hrlysalary=ln(hrlysalary)
g ln_salary=ln(salary)

keep bgtjobid ln_hrlysalary

save job_id_temp, replace

u "Skills_2022-0`num'", clear

egen job=group(bgtjobid)
keep if job<=500000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save temp_skill_first_half, replace 


u "Skills_2022-0`num'", clear

egen job=group(bgtjobid)
keep if job<=1000000 & job>500000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save temp_skill_first_half2, replace 



u "Skills_2022-0`num'", clear

egen job=group(bgtjobid)
keep if job<=1500000 & job>1000000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save temp_skill_first_half3, replace 




u "Skills_2022-0`num'", clear

egen job=group(bgtjobid)
keep if job<=2000000 & job>1500000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save temp_skill_first_half4, replace 



u "Skills_2022-0`num'", clear

egen job=group(bgtjobid)
keep if job<=2500000 & job>2000000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save temp_skill_first_half5, replace 




u "Skills_2022-0`num'", clear

egen job=group(bgtjobid)
keep if job<=3250000 & job>2500000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save temp_skill_first_half6, replace 




u "Skills_2022-0`num'", clear

egen job=group(bgtjobid)
keep if job>3250000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save temp_skill_first_half7, replace 




clear all

append using temp_skill_first_half
append using temp_skill_first_half2
append using temp_skill_first_half3
append using temp_skill_first_half4
append using temp_skill_first_half5
append using temp_skill_first_half6
append using temp_skill_first_half7

save skill_2022_0`num', replace

}





foreach num of numlist 3(1)9 {
u Main_2023-0`num', clear

keep if minhrlysalary!=-999

g hrlysalary=(minhrlysalary+maxhrlysalary)/2
g salary=(minsalary+maxsalary)/2

g ln_hrlysalary=ln(hrlysalary)
g ln_salary=ln(salary)

keep bgtjobid ln_hrlysalary

save job_id_temp, replace

u "Skills_2023-0`num'", clear

egen job=group(bgtjobid)
keep if job<=500000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save temp_skill_first_half, replace 


u "Skills_2023-0`num'", clear

egen job=group(bgtjobid)
keep if job<=1000000 & job>500000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save temp_skill_first_half2, replace 



u "Skills_2023-0`num'", clear

egen job=group(bgtjobid)
keep if job<=1500000 & job>1000000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save temp_skill_first_half3, replace 




u "Skills_2023-0`num'", clear

egen job=group(bgtjobid)
keep if job<=2000000 & job>1500000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save temp_skill_first_half4, replace 



u "Skills_2023-0`num'", clear

egen job=group(bgtjobid)
keep if job<=2500000 & job>2000000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save temp_skill_first_half5, replace 




u "Skills_2023-0`num'", clear

egen job=group(bgtjobid)
keep if job<=3250000 & job>2500000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save temp_skill_first_half6, replace 




u "Skills_2023-0`num'", clear

egen job=group(bgtjobid)
keep if job>3250000
drop job

drop jobdate

merge m:1 bgtjobid using job_id_temp 
keep if _merge==3
drop _merge 

bysort skill: egen total_skill=count(skill)
g neg_total_skill=-total_skill
sort bgtjobid neg_total_skill
by bgtjobid: g rank=_n

drop if rank>20

g skill1=skill if rank==1 
g skill2=skill if rank==2
g skill3=skill if rank==3
g skill4=skill if rank==4
g skill5=skill if rank==5 
g skill6=skill if rank==6
g skill7=skill if rank==7
g skill8=skill if rank==8
g skill9=skill if rank==9
g skill10=skill if rank==10
g skill11=skill if rank==11
g skill12=skill if rank==12
g skill13=skill if rank==13
g skill14=skill if rank==14
g skill15=skill if rank==15
g skill16=skill if rank==16
g skill17=skill if rank==17
g skill18=skill if rank==18
g skill19=skill if rank==19
g skill20=skill if rank==20

by bgtjobid: g first_skill2=skill2[_n+1]
by bgtjobid: g first_skill3=skill3[_n+2]
by bgtjobid: g first_skill4=skill4[_n+3]
by bgtjobid: g first_skill5=skill5[_n+4]
by bgtjobid: g first_skill6=skill6[_n+5]
by bgtjobid: g first_skill7=skill7[_n+6]
by bgtjobid: g first_skill8=skill8[_n+7]
by bgtjobid: g first_skill9=skill9[_n+8]
by bgtjobid: g first_skill10=skill10[_n+9]
by bgtjobid: g first_skill11=skill11[_n+10]
by bgtjobid: g first_skill12=skill12[_n+11]
by bgtjobid: g first_skill13=skill13[_n+12]
by bgtjobid: g first_skill14=skill14[_n+13]
by bgtjobid: g first_skill15=skill15[_n+14]
by bgtjobid: g first_skill16=skill16[_n+15]
by bgtjobid: g first_skill17=skill17[_n+16]
by bgtjobid: g first_skill18=skill18[_n+17]
by bgtjobid: g first_skill19=skill19[_n+18]
by bgtjobid: g first_skill20=skill20[_n+19]


keep if rank==1

drop skill2 skill3 skill4 skill5 skill6 skill7 skill8 skill9 skill10 skill11 skill12 skill13 skill14 skill15 skill16 skill17 skill18 skill19 skill20

replace first_skill2="na" if first_skill2==""
replace first_skill3="na" if first_skill3==""
replace first_skill4="na" if first_skill4==""
replace first_skill5="na" if first_skill5==""
replace first_skill6="na" if first_skill6==""
replace first_skill7="na" if first_skill7==""
replace first_skill8="na" if first_skill8==""
replace first_skill9="na" if first_skill9==""
replace first_skill10="na" if first_skill10==""
replace first_skill11="na" if first_skill11==""
replace first_skill12="na" if first_skill12==""
replace first_skill13="na" if first_skill13==""
replace first_skill14="na" if first_skill14==""
replace first_skill15="na" if first_skill15==""
replace first_skill16="na" if first_skill16==""
replace first_skill17="na" if first_skill17==""
replace first_skill18="na" if first_skill18==""
replace first_skill19="na" if first_skill19==""
replace first_skill20="na" if first_skill20==""

ren first_skill2 skill2 
ren first_skill3 skill3 
ren first_skill4 skill4 
ren first_skill5 skill5 
ren first_skill6 skill6 
ren first_skill7 skill7 
ren first_skill8 skill8 
ren first_skill9 skill9 
ren first_skill10 skill10
ren first_skill11 skill11
ren first_skill12 skill12
ren first_skill13 skill13
ren first_skill14 skill14
ren first_skill15 skill15
ren first_skill16 skill16
ren first_skill17 skill17
ren first_skill18 skill18
ren first_skill19 skill19
ren first_skill20 skill20

drop skill total_skill neg_total_skill rank

save temp_skill_first_half7, replace 




clear all

append using temp_skill_first_half
append using temp_skill_first_half2
append using temp_skill_first_half3
append using temp_skill_first_half4
append using temp_skill_first_half5
append using temp_skill_first_half6
append using temp_skill_first_half7

save skill_2023_0`num', replace

}

*********************************

*** Get the skill vectors for the job posting sample that we use. 


u temp, clear

keep if year>=2018

keep bgtjobid 

save bsample_bgtjobid, replace


foreach num of numlist 1(1)9 {
u skill_2018_0`num', clear

merge 1:1 bgtjobid using bsample_bgtjobid
keep if _merge==3
drop _merge 

save temp_2018_0`num', replace
}

foreach num of numlist 10(1)12 {
u skill_2018_`num', clear

merge 1:1 bgtjobid using bsample_bgtjobid
keep if _merge==3
drop _merge 

save temp_2018_`num', replace
}

foreach num of numlist 1(1)9 {
u skill_2019_0`num', clear

merge 1:1 bgtjobid using bsample_bgtjobid
keep if _merge==3
drop _merge 

save temp_2019_0`num', replace
}

foreach num of numlist 10(1)12 {
u skill_2019_`num', clear

merge 1:1 bgtjobid using bsample_bgtjobid
keep if _merge==3
drop _merge 

save temp_2019_`num', replace
}


foreach num of numlist 1(1)9 {
u skill_2020_0`num', clear

merge 1:1 bgtjobid using bsample_bgtjobid
keep if _merge==3
drop _merge 

save temp_2020_0`num', replace
}

foreach num of numlist 10(1)12 {
u skill_2020_`num', clear

merge 1:1 bgtjobid using bsample_bgtjobid
keep if _merge==3
drop _merge 

save temp_2020_`num', replace
}


foreach num of numlist 1(1)9 {
u skill_2021_0`num', clear

merge 1:1 bgtjobid using bsample_bgtjobid
keep if _merge==3
drop _merge 

save temp_2021_0`num', replace
}

foreach num of numlist 10(1)12 {
u skill_2021_`num', clear

merge 1:1 bgtjobid using bsample_bgtjobid
keep if _merge==3
drop _merge 

save temp_2021_`num', replace
}

foreach num of numlist 1(1)3 {
u skill_2022_0`num', clear

merge 1:1 bgtjobid using bsample_bgtjobid
keep if _merge==3
drop _merge 

save temp_2022_0`num', replace
}

foreach num of numlist 4(1)5 {
u skill_2022_0`num', clear

merge 1:1 bgtjobid using bsample_bgtjobid
keep if _merge==3
drop _merge 

save temp_2022_0`num', replace
}


foreach num of numlist 1(1)9 {
u skill_2023_0`num', clear

merge 1:1 bgtjobid using bsample_bgtjobid
keep if _merge==3
drop _merge 

save temp_2023_0`num', replace
}

foreach num of numlist 10(1)10 {
u skill_2023_`num', clear

merge 1:1 bgtjobid using bsample_bgtjobid
keep if _merge==3
drop _merge 

save temp_2023_`num', replace
}


*** Combine 

clear all
foreach num of numlist 1(1)9 {
append using temp_2018_0`num'
}

foreach num of numlist 10(1)12 {
append using temp_2018_`num'
}


foreach num of numlist 1(1)9 {
append using temp_2019_0`num'
}

foreach num of numlist 10(1)12 {
append using temp_2019_`num'
}


foreach num of numlist 1(1)9 {
append using temp_2020_0`num'
}

foreach num of numlist 10(1)12 {
append using temp_2020_`num'
}


foreach num of numlist 1(1)9 {
append using temp_2021_0`num'
}

foreach num of numlist 10(1)12 {
append using temp_2021_`num'
}

foreach num of numlist 1(1)5 {
append using temp_2022_0`num'
}


drop ln_hrlysalary

save temp_skill, replace
